<?php
class Msg_model extends CI_Model {
    public function __construct() {
        parent::__construct();
        $this->load->database();
    }
    
    public function get_remsg($user_id) {
        // 必须手写查询，否则error
        $sql = '
        SELECT t.*, gender, nickname 
        FROM (
            SELECT post.user_id as post_user_id, post.post_title, r1.repost_id, r1.post_id, r1.user_id, r1.repost_time, r1.repost_content, r1.level_no, r1.re_repost_id, r2.repost_content as re_repost_content, r2.level_no as re_level_no, r1.repost_msg as is_read 
            FROM repost r1 
            JOIN post ON post.post_id = r1.post_id 
            LEFT JOIN repost r2 ON r2.repost_id = r1.re_repost_id 
            WHERE post.user_id = ? AND r1.user_id != ? 
            UNION 
            SELECT post.user_id as post_user_id, post.post_title, r1.repost_id, r1.post_id, r1.user_id, r1.repost_time, r1.repost_content, r1.level_no, r1.re_repost_id, r2.repost_content as re_repost_content, r2.level_no as re_level_no, r1.re_repost_msg as is_read 
            FROM repost r1 
            JOIN repost r2 ON r2.repost_id = r1.re_repost_id 
            JOIN post ON r1.post_id = post.post_id 
            WHERE  post.user_id != ? AND r2.user_id = ? AND r1.user_id != ? 
        ) t 
        JOIN user ON user.user_id = t.user_id 
        ORDER BY t.repost_time DESC;';
        $query = $this->db->query($sql, array($user_id, $user_id, $user_id, $user_id, $user_id));
        if ($query->num_rows() > 0) {
            return $query->result_array();
        } else {
            return NULL;
        }
    }
    
    public function read_all($user_id) {
        $sql1 = '
        UPDATE repost 
        JOIN post ON post.post_id = repost.post_id 
        SET repost_msg = true 
        WHERE repost_msg != true AND post.user_id = ? AND repost.user_id != ?;';
        $sql2 = '
        UPDATE repost r1
        JOIN post ON post.post_id = r1.post_id 
        JOIN repost r2 ON r2.repost_id = r1.re_repost_id 
        SET r1.re_repost_msg = true 
        WHERE r1.re_repost_msg != true AND post.user_id != ? AND r2.user_id = ? AND r1.user_id != ?;';
        
        $this->db->query($sql1, array($user_id, $user_id));
        $this->db->query($sql2, array($user_id, $user_id, $user_id));
    }
    /*
    public function read_re($user_id, $repost_id) {
        $sql1 = '
        UPDATE repost 
        JOIN post ON post.post_id = repost.post_id 
        SET repost_msg = true 
        WHERE repost_id = ? AND post.user_id = ?;';
        $sql2 = '
        UPDATE repost 
        JOIN post ON post.post_id = repost.post_id 
        SET re_repost_msg = true 
        WHERE repost_id = ? AND post.user_id != ?;';
        
        $this->db->query($sql1, array($repost_id, $user_id));
        $this->db->query($sql2, array($repost_id, $user_id));
    }
    */
    public function any_re($user_id) {
        $sql = '
        SELECT COUNT(*) as count 
        FROM (
            SELECT r1.repost_id 
            FROM repost r1 
            JOIN post ON post.post_id = r1.post_id 
            WHERE post.user_id = ? AND r1.user_id != ? AND r1.repost_msg != true 
            UNION 
            SELECT r1.repost_id 
            FROM repost r1 
            JOIN repost r2 ON r2.repost_id = r1.re_repost_id 
            JOIN post ON r1.post_id = post.post_id 
            WHERE  post.user_id != ? AND r2.user_id = ? AND r1.user_id != ? AND r1.re_repost_msg != true 
        ) t;';
        $query = $this->db->query($sql, array($user_id, $user_id, $user_id, $user_id, $user_id))->row_array();
        return $query['count'];
    }
}
?>